package cn.kgc.jdbc.dao;

import cn.kgc.jdbc.utils.DruidUtil;
import cn.kgc.jdbc.vo.ScoreVO;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @author: mosin
 * @version: v1.0  2022/8/29
 */
public class ScoreDaoImpl implements ScoreDao {
    @Override
    public Integer addScore(Integer stuId, Integer courseId, Integer score) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "insert into score (stu_id,course_id,score)values(?,?,?)";
        try {
            int update = queryRunner.update(sql, stuId, courseId, score);
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<ScoreVO> findScoreByCourseId(Integer courseId) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "SELECT student.stu_name ,score.score from student " +
                "LEFT JOIN  score ON student.id = score.stu_id " +
                "WHERE  score.course_id = ? " +
                "ORDER BY score  DESC";
        BasicRowProcessor basicRowProcessor = new BasicRowProcessor(new GenerousBeanProcessor());
        try {
            List<ScoreVO> scoreVOS = queryRunner.query(sql, new BeanListHandler<ScoreVO>(ScoreVO.class, basicRowProcessor), courseId);
            return  scoreVOS;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}
